package com.wyx.jdbc;

import com.mysql.cj.jdbc.Driver;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author 王艺锡
 * @version 1.0
 */
//连接mysql的方式
public class jdbcconnect {
    //方式1
    @Test
    public void connect01() throws SQLException {
        Driver driver = new Driver();
        String url = "jdbc:mysql://localhost:3306/wyx_db02";
        //将用户名和密码存入properties对象
        Properties properties = new Properties();
        //注:user和password为固定写法
        properties.setProperty("user", "root");
        properties.setProperty("password", "wyx");
        //获取connect连接
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);
    }


    //方式2，使用反射
    @Test
    public void connect02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException, NoSuchMethodException, InvocationTargetException {
        //获取Driver的class类对象
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();
        String url = "jdbc:mysql://localhost:3306/wyx_db02";
        Properties properties = new Properties();
        //注:user和password为固定写法
        properties.setProperty("user", "root");
        properties.setProperty("password", "wyx");
        Connection connect = driver.connect(url, properties);
        System.out.println(connect);

    /*    Method connect1 = aClass.getMethod("connect", String.class, Properties.class);
        Connection connection = (Connection) connect1.invoke(driver, url, properties);
        System.out.println(connection);*/
    }

    //方式3
    @Test
    public void connect03() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        //创建url，user，password
        String url = "jdbc:mysql://localhost:3306/wyx_db02";
        String user = "root";
        String password = "wyx";

        //注册Driver驱动
        DriverManager.registerDriver(driver);
        //获取connection
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }

    //方法4 -- 最常用
    @Test
    public void connect04() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        //使用反射加载了Driver类
        //在加载Driver类时，完成注册
        /*
        源码: 1.静态代码块，在类加载的时候会被调用一次，其中完成了对Driver的register
             2.DriverManager.registerDriver(new Driver());
          static {
                    try {
                        DriverManager.registerDriver(new Driver());
                    } catch (SQLException var1) {
                        throw new RuntimeException("Can't register driver!");
                    }
               }
         */

        Class.forName("com.mysql.cj.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/wyx_db02";
        String user = "root";
        String password = "wyx";

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }


    //方式5，改进方法4，用配置文件，使连接数据库更加灵活
    @Test
    public void connect05() throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileReader("src\\mysql.properties"));

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);//可以不写

        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }

    @Test
    public void exercise() throws IOException, ClassNotFoundException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileReader("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement();
        String str = "create table news(" +
                "id int," +
                "content text);";
        String str1 = "insert into news values (1,'上海新闻')";
        String str2 = "insert into news values (2,'北京新闻')";
        String str3 = "insert into news values (3,'河南新闻')";
        String str4 = "insert into news values (4,'深圳新闻')";
        String str5 = "insert into news values (5,'武汉新闻')";

        String str6 = "update news set content = '王艺锡' where id = 1";

        String str7 = "delete from news where id = 3";
        int rows = statement.executeUpdate(str7);
        System.out.println(rows > 0 ? "成功" : "失败");
    }


}
